/**
 * 系统数据库插件 API 接口
 * 提供给其他插件调用的方法
 */
import { Sequelize, DataTypes } from 'sequelize'
import config from './config.js'

// 初始化数据库连接
const sequelize = config.dialect === 'sqlite'
  ? new Sequelize({ dialect: 'sqlite', storage: config.storage, logging: false })
  : config.dialect === 'postgres'
    ? new Sequelize(
        config.postgres.database,
        config.postgres.username,
        config.postgres.password,
        {
          host: config.postgres.host,
          port: config.postgres.port,
          dialect: 'postgres',
          logging: false,
          ssl: config.postgres.ssl,
          dialectOptions: config.postgres.ssl ? {
            ssl: {
              require: true,
              rejectUnauthorized: false
            }
          } : {}
        }
      )
    : new Sequelize(
        config.mysql.database,
        config.mysql.username,
        config.mysql.password,
        {
          host: config.mysql.host,
          port: config.mysql.port,
          dialect: 'mysql',
          logging: false
        }
      )

// 定义模型
// 用户金币模型
const UserGold = sequelize.define('UserGold', {
  userId: { type: DataTypes.STRING, primaryKey: true },
  gold: { type: DataTypes.INTEGER, defaultValue: 0 },
  lastSignIn: { type: DataTypes.DATE },
  consecutiveDays: { type: DataTypes.INTEGER, defaultValue: 0 }
})

// 用户道具模型
const UserItem = sequelize.define('UserItem', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  userId: { type: DataTypes.STRING },
  itemId: { type: DataTypes.STRING },
  count: { type: DataTypes.INTEGER, defaultValue: 1 }
})

// 道具信息模型
const ItemInfo = sequelize.define('ItemInfo', {
  id: { type: DataTypes.STRING, primaryKey: true },
  name: { type: DataTypes.STRING },
  description: { type: DataTypes.TEXT },
  price: { type: DataTypes.INTEGER },
  type: { type: DataTypes.STRING }
})

// 用户等级模型
const UserLevel = sequelize.define('UserLevel', {
  userId: { type: DataTypes.STRING, primaryKey: true },
  level: { type: DataTypes.INTEGER, defaultValue: 1 }
})

// 用户UID模型
const UserUID = sequelize.define('UserUID', {
  userId: { type: DataTypes.STRING, primaryKey: true },
  uid: { type: DataTypes.STRING },
  totalDays: { type: DataTypes.INTEGER, defaultValue: 0 }
})

// 用户好感度模型
const UserFavor = sequelize.define('UserFavor', {
  userId: { type: DataTypes.STRING, primaryKey: true },
  favorValue: { type: DataTypes.INTEGER, defaultValue: 0 },
  lastInteract: { type: DataTypes.DATE }
})

// 通用键值对存储模型
const UserKeyValue = sequelize.define('UserKeyValue', {
  userId: { type: DataTypes.STRING, primaryKey: true },
  key: { type: DataTypes.STRING, primaryKey: true },
  value: { type: DataTypes.JSON, defaultValue: null },
  lastUpdated: { type: DataTypes.DATE, defaultValue: DataTypes.NOW }
})

// 用户权限模型
const UserPermission = sequelize.define('UserPermission', {
  userId: { type: DataTypes.STRING, primaryKey: true },
  permissionLevel: { type: DataTypes.INTEGER, defaultValue: 0 },
  lastModified: { type: DataTypes.DATE, defaultValue: DataTypes.NOW }
})

// 用户昵称模型
const UserNickname = sequelize.define('UserNickname', {
  userId: { type: DataTypes.STRING, primaryKey: true },
  nickname: { type: DataTypes.STRING, defaultValue: '' },
  lastModified: { type: DataTypes.DATE, defaultValue: DataTypes.NOW }
})

// 同步数据库并初始化计数器
sequelize.sync().then(async () => {
  // 检查是否存在计数器
  const counter = await UserUID.findOne({ where: { userId: 'counter' } })
  if (!counter) {
    // 创建计数器，初始值为0
    await UserUID.create({
      userId: 'counter',
      uid: '0',
      totalDays: 0
    })
    console.log('UID计数器已初始化')
  }
})

// 自定义模型管理
// 存储已注册的自定义模型
const _customModels = new Map();

/**
 * 注册自定义模型
 * @param {string} modelName - 模型名称
 * @param {object} modelDefinition - 模型定义
 * @param {object} options - 可选配置项
 * @returns {boolean} 注册是否成功
 */
async function registerCustomModel(modelName, modelDefinition, options = {}) {
  if (_customModels.has(modelName)) {
    console.warn(`模型 ${modelName} 已存在，将被覆盖`);
  }

  // 定义模型
  const Model = sequelize.define(modelName, modelDefinition, {
    timestamps: options.timestamps !== undefined ? options.timestamps : true,
    indexes: modelDefinition.indexes || [],
    ...options
  });

  // 同步模型到数据库
  if (options.autoSync !== false) {
    await Model.sync(options.syncOptions || {});
  }

  // 存储模型引用
  _customModels.set(modelName, Model);

  return true;
}

/**
 * 批量存储数据到自定义模型
 * @param {string} modelName - 模型名称
 * @param {Array} dataArray - 数据数组
 * @param {object} options - 选项
 * @returns {Promise<Array>} 存储结果
 */
async function bulkSaveData(modelName, dataArray, options = {}) {
  if (!_customModels.has(modelName)) {
    throw new Error(`模型 ${modelName} 未注册`);
  }

  const Model = _customModels.get(modelName);
  return await Model.bulkCreate(dataArray, options);
}

/**
 * 批量读取自定义模型数据
 * @param {string} modelName - 模型名称
 * @param {object} where - 查询条件
 * @param {object} options - 选项（包括排序、分页等）
 * @returns {Promise<Array>} 查询结果
 */
async function bulkReadData(modelName, where = {}, options = {}) {
  if (!_customModels.has(modelName)) {
    throw new Error(`模型 ${modelName} 未注册`);
  }

  const Model = _customModels.get(modelName);
  return await Model.findAll({
    where,
    ...options
  });
}

/**
 * 保存群组用户数据
 * @param {string} groupId - 群组ID
 * @param {Array} userIds - 用户ID数组
 * @returns {Promise<boolean>} 操作是否成功
 */
async function saveGroupUsers(groupId, userIds) {
    // 注册群组用户模型（如果不存在）
    await registerCustomModel('GroupUser', {
      id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
      groupId: { type: DataTypes.STRING, index: true },
      userId: { type: DataTypes.STRING, index: true },
      joinedAt: { type: DataTypes.DATE, defaultValue: DataTypes.NOW }
    }, {
      indexes: [{
        unique: true,
        fields: ['groupId', 'userId']
      }]
    });

    // 准备数据
    const data = userIds.map(userId => ({
      groupId,
      userId: userId.toString()
    }));

  // 批量存储
  await bulkSaveData('GroupUser', data, {
    updateOnDuplicate: ['joinedAt']
  });

  return true;
}

/**
 * 获取群组所有用户
 * @param {string} groupId - 群组ID
 * @returns {Promise<Array>} 用户ID数组
 */
async function getGroupUsers(groupId) {
  if (!_customModels.has('GroupUser')) {
    return [];
  }

  const users = await bulkReadData('GroupUser', { groupId });
  return users.map(user => user.userId);
}

/**
 * 创建排行榜
 * @param {string} leaderboardName - 排行榜名称
 * @param {Array} data - 排行榜数据
 * @returns {Promise<boolean>} 操作是否成功
 */
async function createLeaderboard(leaderboardName, data) {
  // 注册排行榜模型
  await registerCustomModel(leaderboardName, {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    userId: { type: DataTypes.STRING, index: true },
    score: { type: DataTypes.INTEGER, defaultValue: 0 },
    rank: { type: DataTypes.INTEGER },
    updatedAt: { type: DataTypes.DATE, defaultValue: DataTypes.NOW }
  });

  // 批量存储
  await bulkSaveData(leaderboardName, data, {
    updateOnDuplicate: ['score', 'rank', 'updatedAt']
  });

  return true;
}

/**
 * 获取排行榜
 * @param {string} leaderboardName - 排行榜名称
 * @param {number} limit - 获取数量
 * @param {object} options - 额外选项
 * @returns {Promise<Array>} 排行榜数据
 */
async function getLeaderboard(leaderboardName, limit = 10, options = {}) {
  if (!_customModels.has(leaderboardName)) {
    return [];
  }

  return await bulkReadData(leaderboardName, {}, {
    order: [['score', 'DESC']],
    limit,
    ...options
  });
}

// API 方法
export default {
  // 自定义模型相关
  registerCustomModel,
  bulkSaveData,
  bulkReadData,
  saveGroupUsers,
  getGroupUsers,
  createLeaderboard,
  getLeaderboard,
  // 权限相关
  async getUserPermission(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userPermission = await UserPermission.findOne({ where: { userId: userId.toString() } }) ||
      await UserPermission.create({ userId: userId.toString() })
    return userPermission.permissionLevel
  },

  async setUserPermission(userId, level) {
    // 将userId转换为字符串以匹配数据库类型
    const userPermission = await UserPermission.findOne({ where: { userId: userId.toString() } }) ||
      await UserPermission.create({ userId: userId.toString() })
    userPermission.permissionLevel = level
    userPermission.lastModified = new Date()
    await userPermission.save()
    return userPermission.permissionLevel
  },

  async upgradeUserPermission(userId, amount = 1) {
    // 将userId转换为字符串以匹配数据库类型
    const userPermission = await UserPermission.findOne({ where: { userId: userId.toString() } }) ||
      await UserPermission.create({ userId: userId.toString() })
    userPermission.permissionLevel += amount
    userPermission.lastModified = new Date()
    await userPermission.save()
    return userPermission.permissionLevel
  },

  async downgradeUserPermission(userId, amount = 1) {
    // 将userId转换为字符串以匹配数据库类型
    const userPermission = await UserPermission.findOne({ where: { userId: userId.toString() } }) ||
      await UserPermission.create({ userId: userId.toString() })
    userPermission.permissionLevel = Math.max(0, userPermission.permissionLevel - amount)
    userPermission.lastModified = new Date()
    await userPermission.save()
    return userPermission.permissionLevel
  },

  // 昵称相关
  async getUserNickname(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userNickname = await UserNickname.findOne({ where: { userId: userId.toString() } }) ||
      await UserNickname.create({ userId: userId.toString() })
    return userNickname.nickname
  },

  async setUserNickname(userId, nickname) {
    // 将userId转换为字符串以匹配数据库类型
    const userNickname = await UserNickname.findOne({ where: { userId: userId.toString() } }) ||
      await UserNickname.create({ userId: userId.toString() })
    userNickname.nickname = nickname
    userNickname.lastModified = new Date()
    await userNickname.save()
    return userNickname.nickname
  },

  // UID相关
  async getUserTotalSignInDays(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userUID = await UserUID.findOne({ where: { userId: userId.toString() } })
    return userUID ? userUID.totalDays : 0
  },

  /**
   * 通过UID获取用户ID
   * @param {string} uid - 用户UID
   * @returns {Promise<string|null>} 用户ID，如果不存在则返回null
   */
  async getUserIdByUID(uid) {
    const userUID = await UserUID.findOne({ where: { uid } })
    return userUID ? userUID.userId : null
  },

  async updateUserTotalSignInDays(userId) {
    // 将userId转换为字符串以匹配数据库类型
    let userUID = await UserUID.findOne({ where: { userId: userId.toString() } })
    if (!userUID) {
      // 如果用户不存在，创建新记录
      const uid = await this.getUserUID(userId)
      userUID = await UserUID.findOne({ where: { userId: userId.toString() } })
    }
    userUID.totalDays += 1
    await userUID.save()
    return userUID.totalDays
  },

  async getUserUID(userId) {
    // 检查用户是否已经有UID
    // 将userId转换为字符串以匹配数据库类型
    let userUID = await UserUID.findOne({ where: { userId: userId.toString() } })

    if (userUID) {
      return userUID.uid
    }

    // 如果是新用户，生成新的UID
    // 获取当前计数器值
    const counter = await UserUID.findOne({ where: { userId: 'counter' } })
    let currentCount = parseInt(counter.uid)
    currentCount += 1

    // 格式化UID为9位数字
    const newUID = currentCount.toString().padStart(9, '0')

    // 更新计数器
    counter.uid = currentCount.toString()
    await counter.save()

    // 创建新用户UID记录
    await UserUID.create({
      userId: userId.toString(),
      uid: newUID,
      totalDays: 0
    })

    return newUID
  },

  // 金币相关
  async getUserGold(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userGold = await UserGold.findOne({ where: { userId: userId.toString() } }) ||
      await UserGold.create({ userId: userId.toString() })
    return userGold.gold
  },

  async getUserSignInInfo(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userGold = await UserGold.findOne({ where: { userId: userId.toString() } }) ||
      await UserGold.create({ userId: userId.toString() })
    return {
      lastSignIn: userGold.lastSignIn,
      consecutiveDays: userGold.consecutiveDays
    }
  },

  async updateSignInInfo(userId, reward) {
    // 将userId转换为字符串以匹配数据库类型
    const userGold = await UserGold.findOne({ where: { userId: userId.toString() } }) ||
      await UserGold.create({ userId: userId.toString() })
    const now = new Date()
    const today = new Date(now.getFullYear(), now.getMonth(), now.getDate())

    // 检查是否已经签到
    if (userGold.lastSignIn) {
      const lastSignInDate = new Date(userGold.lastSignIn.getFullYear(), userGold.lastSignIn.getMonth(), userGold.lastSignIn.getDate())
      // 如果是同一天，不增加连续天数
      if (lastSignInDate.getTime() === today.getTime()) {
        return {
          alreadySigned: true,
          gold: userGold.gold,
          consecutiveDays: userGold.consecutiveDays
        }
      }
      // 如果是昨天，增加连续天数
      const yesterday = new Date(today)
      yesterday.setDate(yesterday.getDate() - 1)
      if (lastSignInDate.getTime() === yesterday.getTime()) {
        userGold.consecutiveDays += 1
      } else {
        // 否则重置连续天数
        userGold.consecutiveDays = 1
      }
    } else {
      // 首次签到
      userGold.consecutiveDays = 1
    }

    // 更新签到时间和金币
    userGold.lastSignIn = now
    userGold.gold += reward
    await userGold.save()

    return {
      alreadySigned: false,
      gold: userGold.gold,
      consecutiveDays: userGold.consecutiveDays
    }
  },

  async addUserGold(userId, amount) {
    // 将userId转换为字符串以匹配数据库类型
    const userGold = await UserGold.findOne({ where: { userId: userId.toString() } }) ||
      await UserGold.create({ userId: userId.toString() })
    userGold.gold += amount
    await userGold.save()
    return userGold.gold
  },

  async reduceUserGold(userId, amount) {
    // 将userId转换为字符串以匹配数据库类型
    const userGold = await UserGold.findOne({ where: { userId: userId.toString() } })
    if (!userGold || userGold.gold < amount) return false
    userGold.gold -= amount
    await userGold.save()
    return true
  },

  async setUserGold(userId, amount) {
    // 将userId转换为字符串以匹配数据库类型
    const userGold = await UserGold.findOne({ where: { userId: userId.toString() } }) ||
      await UserGold.create({ userId: userId.toString() })
    userGold.gold = amount
    await userGold.save()
    return userGold.gold
  },

  // 道具相关
  async getUserItems(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userItems = await UserItem.findAll({ where: { userId: userId.toString() } })
    const result = []
    for (const item of userItems) {
      const itemInfo = await ItemInfo.findOne({ where: { id: item.itemId } })
      if (itemInfo) {
        result.push({
          id: itemInfo.id,
          name: itemInfo.name,
          description: itemInfo.description,
          count: item.count
        })
      }
    }
    return result
  },

  async addUserItem(userId, itemId) {
    // 将userId转换为字符串以匹配数据库类型
    const userItem = await UserItem.findOne({ where: { userId: userId.toString(), itemId } })
    if (userItem) {
      userItem.count += 1
      await userItem.save()
    } else {
      await UserItem.create({ userId: userId.toString(), itemId })
    }
    return true
  },

  async reduceUserItem(userId, itemId) {
    // 将userId转换为字符串以匹配数据库类型
    const userItem = await UserItem.findOne({ where: { userId: userId.toString(), itemId } })
    if (!userItem || userItem.count <= 0) return false
    userItem.count -= 1
    if (userItem.count === 0) {
      await userItem.destroy()
    } else {
      await userItem.save()
    }
    return true
  },

  async addItemInfo(itemData) {
    await ItemInfo.upsert(itemData)
    return true
  },

  async getAllItems() {
    return await ItemInfo.findAll()
  },

  // 通过名称获取道具信息
  getItemInfoByName: async function(name) {
    // 确保 this 上下文正确
    const items = await this.getAllItems()
    return items.find(item => item.name === name)
  },

  // 等级相关
  async getUserLevel(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userLevel = await UserLevel.findOne({ where: { userId: userId.toString() } }) ||
      await UserLevel.create({ userId: userId.toString() })
    return userLevel.level
  },

  async addUserLevel(userId, amount) {
    // 将userId转换为字符串以匹配数据库类型
    const userLevel = await UserLevel.findOne({ where: { userId: userId.toString() } }) ||
      await UserLevel.create({ userId: userId.toString() })
    userLevel.level += amount
    await userLevel.save()
    return userLevel.level
  },

  async reduceUserLevel(userId, amount) {
    // 将userId转换为字符串以匹配数据库类型
    const userLevel = await UserLevel.findOne({ where: { userId: userId.toString() } })
    if (!userLevel || userLevel.level < amount) return false
    userLevel.level -= amount
    await userLevel.save()
    return true
  },

  // 好感度相关
  async getUserFavor(userId) {
    // 将userId转换为字符串以匹配数据库类型
    const userFavor = await UserFavor.findOne({ where: { userId: userId.toString() } }) ||
      await UserFavor.create({ userId: userId.toString() })
    return userFavor.favorValue
  },

  async addUserFavor(userId, amount) {
    // 将userId转换为字符串以匹配数据库类型
    const userFavor = await UserFavor.findOne({ where: { userId: userId.toString() } }) ||
      await UserFavor.create({ userId: userId.toString() })
    userFavor.favorValue += amount
    userFavor.lastInteract = new Date()
    await userFavor.save()
    return userFavor.favorValue
  },

  async reduceUserFavor(userId, amount) {
    // 将userId转换为字符串以匹配数据库类型
    const userFavor = await UserFavor.findOne({ where: { userId: userId.toString() } })
    if (!userFavor || userFavor.favorValue < amount) return false
    userFavor.favorValue -= amount
    userFavor.lastInteract = new Date()
    await userFavor.save()
    return true
  },

  // 通用键值对存储相关
  async setUserKeyValue(userId, key, value) {
    // 将userId转换为字符串以匹配数据库类型
    const [userKV, created] = await UserKeyValue.findOrCreate({
      where: { userId: userId.toString(), key },
      defaults: { userId: userId.toString(), value, lastUpdated: new Date() }
    })

    if (!created) {
      userKV.value = value
      userKV.lastUpdated = new Date()
      await userKV.save()
    }

    return { success: true, created }
  },

  async getUserKeyValue(userId, key) {
    // 将userId转换为字符串以匹配数据库类型
    const userKV = await UserKeyValue.findOne({ where: { userId: userId.toString(), key } })
    return userKV ? userKV.value : null
  },

  async deleteUserKeyValue(userId, key) {
    // 将userId转换为字符串以匹配数据库类型
    const result = await UserKeyValue.destroy({ where: { userId: userId.toString(), key } })
    return result > 0
  },

  async incrementUserKeyValue(userId, key, amount = 1) {
    // 将userId转换为字符串以匹配数据库类型
    const userKV = await UserKeyValue.findOne({ where: { userId: userId.toString(), key } })
    if (!userKV || typeof userKV.value !== 'number') return false

    userKV.value += amount
    userKV.lastUpdated = new Date()
    await userKV.save()
    return userKV.value
  }
}